using MySql.Data.MySqlClient;
using System.Data;

namespace AdminApiTemplate.Common.Helpers
{
    public class MySqlHelper
    {
        public readonly string _connStr;

        public MySqlHelper(string connStr)
        {
            _connStr = connStr;
        }

        public int Execute(string sql, object obj = null)
        {
            using (var conn = new MySqlConnection(_connStr))
            {
                conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;

                var dict = CreateParameters(obj);              
                var sps = CreateParametersWithDict(dict);
                if (sps != null)
                {
                    cmd.Parameters.AddRange(sps);
                }

                var rows = cmd.ExecuteNonQuery();
                return rows;
            }
        }

        public T ExecuteScalar<T>(string sql, object obj = null)
        {
            using (var conn = new MySqlConnection(_connStr))
            {
                conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;

                var dict = CreateParameters(obj);
                var sps = CreateParametersWithDict(dict);
                if (sps != null)
                {
                    cmd.Parameters.AddRange(sps);
                }

                var dbVal = cmd.ExecuteScalar();
                if (dbVal!=null)
                {
                    var val = ConvertHelper.GetVal<T>(dbVal.ToString());
                    return val;
                }

                return default(T);
            }
        }

        public List<T> GetList<T>(string sql, object obj = null)
        {
            var list = new List<T>();
            var ttype = typeof(T);
            var tps = ttype.GetProperties();
            var dt = GetDataTable(sql, obj);
            foreach (DataRow row in dt.Rows)
            {
                var one = Activator.CreateInstance<T>();
                foreach (var tp in tps)
                {
                    if (!HasColumn(row, tp.Name))
                    {
                        continue;
                    }

                    var dbVal = row[tp.Name];
                    if (dbVal == DBNull.Value || dbVal == null)
                    {
                        continue;
                    }

                    var val = ConvertHelper.GetValFromDb(tp, dbVal);
                    tp.SetValue(one, val, null);
                }
                list.Add(one);
            }

            return list;
        }

        public DataTable GetDataTable(string sql, object obj = null)
        {
            var table = new DataTable();
            using (var adapter = new MySqlDataAdapter(sql, _connStr))
            {
                var dict = CreateParameters(obj);
                var sps = CreateParametersWithDict(dict);
                if (sps != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(sps);
                }
                adapter.Fill(table);
                return table;
            }
        }

        private bool HasColumn(DataRow row, string columnName)
        {
            for (var i = 0; i < row.Table.Columns.Count; i++)
            {
                if (row.Table.Columns[i].ColumnName.ToLower() == columnName.ToLower())
                {
                    return true;
                }
            }

            return false;
        }

        private Dictionary<string, object> CreateParameters(object obj)
        {
            var dict = obj as Dictionary<string, object>;
            if (dict == null)
            {
                dict = ConvertHelper.ToDictionary(obj);
            }
            return dict;
        }

        private MySqlParameter[] CreateParametersWithDict(Dictionary<string, object> dict)
        {
            if (dict == null)
            {
                return null;
            }

            var sps = new List<MySqlParameter>();
            foreach (var key in dict.Keys)
            {
                var val = dict[key];
                var sp = new MySqlParameter
                {
                    ParameterName = key,
                    MySqlDbType = MySqlDbType.String,
                    Value = val
                };

                if (val == null)
                {
                    sp.Value = DBNull.Value;
                    sp.IsNullable = true;
                }
                else
                {
                    sp.MySqlDbType = ConvertDbType(val.GetType());
                    sp.Value = val;
                }

                if (sp.MySqlDbType == MySqlDbType.String)
                {
                    if (val == null)
                    {
                        sp.Size = 8;
                    }
                    else
                    {
                        sp.Size = val.ToString().Length + 8;
                    }
                }

                sps.Add(sp);
            }
            return sps.ToArray();
        }

        private MySqlDbType ConvertDbType(Type t)
        {
            if (t == typeof(int) || t == typeof(int?))
            {
                return MySqlDbType.Int32;
            }

            if (t == typeof(double) || t == typeof(double?))
            {
                return MySqlDbType.Double;
            }

            if (t == typeof(decimal) || t == typeof(decimal?))
            {
                return MySqlDbType.Decimal;
            }

            if (t == typeof(bool) || t == typeof(bool?))
            {
                return MySqlDbType.Bit;
            }

            if (t == typeof(DateTime) || t == typeof(DateTime?))
            {
                return MySqlDbType.DateTime;
            }

            return MySqlDbType.String;
        }
    }
}
